package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.Project;
import com.zzmcc.jms.model.ProjectBuild;
import com.zzmcc.sys.model.Userinfo;
import util.ChristStringUtil;
import util.DateUtil;
import util.ModelUtils;
import util.StringUtil;

import java.util.*;

public class ProjectBuildController0804 extends BaseController {
    private static final Logger log =  Logger.getLogger(ProjectBuildController0804.class);

	public void index() {
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String name = getPara("project.name");
		String code = getPara("project.code");
		String projectOwner = getPara("project.projectOwner");
		String demandOwner = getPara("project.demandOwner");
		String homeCounty = getPara("project.homeCounty");
		String isProject = getPara("project.isProject");
		String share = getPara("project.share");
		String taskMonth = getPara("project.taskMonth");
		String isCm = getPara("project.isCm");
		String isCu = getPara("project.isCu");
		String isCt = getPara("project.isCt");
		String isDesign = getPara("project.isDesign");
		String isReach = getPara("project.isReach");
		String isStart = getPara("project.isStart");
		String isComplete = getPara("project.isComplete");
		String isArchive = getPara("project.isArchive");
		String isSubmit = getPara("project.isSubmit");
		String demandMemo = getPara("project.demandMemo");
		String modifyDate = getPara("project.modifyDate");
		String planCompleteDate = getPara("project.planCompleteDate");
		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}
		if(ChristStringUtil.isNotEmpty(projectOwner)){
			whee.append(" and projectOwner like ?");
			param.add("%" + projectOwner + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner like ?");
			param.add("%" + demandOwner + "%");
		}
		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and a.code like ?");
			param.add("%" + code + "%");
		}
		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}

		if(ChristStringUtil.isNotEmpty(share)){
			whee.append(" and share = ?");
			param.add(share);
		}

		if(ChristStringUtil.isNotEmpty(taskMonth)){
			whee.append(" and taskMonth = ?");
			param.add(taskMonth);
		}

		if(ChristStringUtil.isNotEmpty(isProject)){
			whee.append(" and coalesce(a.isProject,'否')= ?");
			param.add(isProject);
		}
		if(ChristStringUtil.isNotEmpty(isDesign)){
			whee.append(" and coalesce(b.isDesign,'否') = ?");
			param.add(isDesign);
		}

		if(ChristStringUtil.isNotEmpty(isReach)){
			whee.append(" and coalesce(b.isReach,'否') = ?");
			param.add(isReach);
		}
		if(ChristStringUtil.isNotEmpty(isStart)){
			whee.append(" and coalesce(b.isStart,'否') = ?");
			param.add(isStart);
		}
		if(ChristStringUtil.isNotEmpty(isComplete)){
			whee.append(" and coalesce(b.isComplete,'否') = ?");
			param.add(isComplete);
		}
		if(ChristStringUtil.isNotEmpty(isSubmit)){
			whee.append(" and coalesce(b.isSubmit,'否') = ?");
			param.add(isSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isArchive)){
			whee.append(" and coalesce(b.isArchive,'否') = ?");
			param.add(isArchive);
		}
		if(ChristStringUtil.isNotEmpty(isCm)){
			whee.append(" and isCm = ?");
			param.add(isCm);
		}
		if(ChristStringUtil.isNotEmpty(isCt)){
			whee.append(" and isCt = ?");
			param.add(isCt);
		}
		if(ChristStringUtil.isNotEmpty(isCu)){
			whee.append(" and isCu = ?");
			param.add(isCu);
		}
		if(ChristStringUtil.isNotEmpty(modifyDate)){
			whee.append(" and TO_DAYS(b.modifyDate) >= TO_DAYS(?)");
			param.add( modifyDate );
		}
		if(ChristStringUtil.isNotEmpty(planCompleteDate)){
			whee.append(" and TO_DAYS(b.planCompleteDate) = TO_DAYS(?)");
			param.add( planCompleteDate );
		}

		Page<ProjectBuild> pager = ProjectBuild.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
				"select a.id,a.homeCounty,a.name,a.code,a.longitude,a.latitude,a.addr,a.share,a.projectDate,a.projectOwner,a.isProject,isCm,isCt,isCu,a.memo," +
						"b.modifyDate,b.modifyMemo,b.memo buildMemo," +
						"isDesign,isReach,isStart,isComplete,isPass,isSubmit,b.submitDate,isArchive ",
				" from jms_project a left join jms_project_build b on b.pid=a.pid " +
						" where COALESCE(a.isPlus,'否')='否' and a.isTask='是' and a.isSelectConfirm='是' "+ whee.toString()+" order by b.modifyDate desc",
				param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("projectOwner",projectOwner);
		setAttr("demandOwner",demandOwner);
		setAttr("code",code);
		setAttr("homeCounty",homeCounty);
		setAttr("isProject",isProject);
		setAttr("share",share);
		setAttr("taskMonth",taskMonth);
		setAttr("isCm",isCm);
		setAttr("isCu",isCu);
		setAttr("isCt",isCt);
		setAttr("isDesign",isDesign);
		setAttr("isReach",isReach);
		setAttr("isStart",isStart);
		setAttr("isComplete", isComplete);
		setAttr("isArchive",isArchive);
		setAttr("isSubmit",isSubmit);
		setAttr("isCm",isCm);
		setAttr("isCt",isCt);
		setAttr("isCu",isCu);
		setAttr("modifyDate",modifyDate);
		setAttr("demandMemo",demandMemo);
		setAttr("planCompleteDate",planCompleteDate);
		render("../project_build_list.html");
	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		String id = getPara("id");
		Userinfo loginUser =getSessionAttr("loginUser");
		String modifyUser=loginUser.get("realname");
		int roleid=loginUser.get("roleid");

		String sql="select a.name,a.pid,a.code,a.baseDate,a.projectOwner," +
				"b.id,b.isReach,isDesign,b.isStart,b.isComplete,b.isPass,b.isSubmit,planCompleteDate," +
				"b.modifyMemo,b.memo,reachDate,b.isGuard,b.guardReason," +
				"coalesce(b.designDate,date_add(a.baseDate,INTERVAL 3 day)) designDate,\n" +
				"coalesce(b.startDate,date_add(a.baseDate,INTERVAL 6 day)) startDate,\n" +
				"coalesce(b.completeDate,date_add(a.baseDate,INTERVAL 46 day)) completeDate,\n" +
				"coalesce(b.submitDate,date_add(a.baseDate,INTERVAL 56 day)) submitDate" +
				" from jms_project a left join jms_project_build b on b.pid=a.pid " +
				" where a.id=?";
		ProjectBuild m= ProjectBuild.dao.findFirst(sql,id);
		//管理员及区域负责人才有权限修改
		if (roleid==0||(StringUtil.isNotEmpty(modifyUser)&&modifyUser.equals(m.getStr("projectOwner")))){
			setAttr("modifyUser",modifyUser);
			setAttr("roleid",roleid);
			setAttr("projectBuild",m);
			render("../project_bulid_input.html");
		} else {
			render("../project_operate_tip.html");
		}
	}

	public void update() {
		ProjectBuild m = getModel(ProjectBuild.class, "projectBuild");

		//更新状态，同时更新完成时间
		String isReach = m.getStr("isReach");
		if (StringUtil.isNotEmpty(isReach) && isReach.equals("是")){
			m.set("reachDate", new Date());
		}
		String isDesign = m.getStr("isDesign");
		if (StringUtil.isNotEmpty(isDesign) && isDesign.equals("是")){
			m.set("designDate", new Date());
		}
		String isStart = m.getStr("isStart");
		if (StringUtil.isNotEmpty(isStart) && isStart.equals("是")){
			m.set("startDate", new Date());
		}
		String isComplete = m.getStr("isComplete");
		if (StringUtil.isNotEmpty(isComplete) && isComplete.equals("是")){
			m.set("completeDate", new Date());
		}
		String isSubmit = m.getStr("isSubmit");
		if (StringUtil.isNotEmpty(isSubmit) && isSubmit.equals("是")){
			m.set("submitDate", new Date());
		}

		ModelUtils.fillForUpdate(m);
		m.update();
		renderDWZSuccessJson("修改成功！");

	}

	public void save() {
		try {
			ProjectBuild m = getModel(ProjectBuild.class, "projectBuild");

			//更新状态，同时更新完成时间
			String isReach = m.getStr("isReach");
			if (StringUtil.isNotEmpty(isReach) && isReach.equals("是")){
				m.set("reachDate", new Date());
			}
			String isDesign = m.getStr("isDesign");
			if (StringUtil.isNotEmpty(isDesign) && isDesign.equals("是")){
				m.set("designDate", new Date());
			}
			String isStart = m.getStr("isStart");
			if (StringUtil.isNotEmpty(isStart) && isStart.equals("是")){
				m.set("startDate", new Date());
			}
			String isComplete = m.getStr("isComplete");
			if (StringUtil.isNotEmpty(isComplete) && isComplete.equals("是")){
				m.set("completeDate", new Date());
			}
			String isSubmit = m.getStr("isSubmit");
			if (StringUtil.isNotEmpty(isSubmit) && isSubmit.equals("是")){
				m.set("submitDate", new Date());
			}

			ModelUtils.fillForUpdate(m);
			m.save();
			renderDWZSuccessJson("添加成功！");
		}catch (Exception e){
			renderDWZSuccessJson("重复提交，失败！");
		}
	}

	@Before(AuthorizeInterceptor.class)
	public void calculate(){
		render("../project_build_cal.html");
	}

	/**
	 * 统计入库
	 */
	@Before(AuthorizeInterceptor.class)
	public void imp(){
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String modifyDate = getPara("project.modifyDate");
		String sumDate = DateUtil.dateToStr(DateUtil.strToDate(modifyDate));

		Db.update("delete a from jms_project_build_detail a where sumDate =?",sumDate);

		if(ChristStringUtil.isNotEmpty(modifyDate)){
			whee.append(" and b.modifyDate < ?");
			param.add( modifyDate );
		}

		Record rMax = Db.findFirst("select max(modifyDate) maxModifyDate from jms_project_build_detail");
		Date lastImpDate=rMax.get("maxModifyDate");
		if(null!=lastImpDate){
			whee.append(" and b.modifyDate > ?");
			param.add(lastImpDate);
		}
		//统计日有更新站点
		String sql="select  "+sumDate+" sumDate,a.homeCounty,a.name,a.code,a.longitude,a.latitude,a.addr,a.demandOwner,a.share,\n" +
				"a.isProject,a.projectDate,a.projectOwner,a.isCm,a.isCt,a.isCu, \n" +
				"b.isReach,b.isStart,b.isDesign,b.isComplete,b.isSubmit,b.modifyDate,b.modifyMemo,b.memo\n" +
				" from jms_project a\n" +
				"left join\n" +
				"jms_project_build b\n" +
				"on a.pid=b.pid \n" +
				" where COALESCE(b.isArchive,'否')='否' and a.isSelectConfirm='是' "+ whee.toString()+"order by b.modifyDate desc";
		List<Record> buildDetail = Db.find(sql,param.toArray());

		for(Record r:buildDetail){
			Db.save("jms_project_build_detail",r);
		}

		//统计日进度汇总
		sql = "select " + sumDate + " sumDate,homeCounty,regCnt,\n" +
				"reachCnt,reachCnt/regCnt reachRate,\n" +
				"designCnt,designCnt/regCnt designRate,\n" +
				"startCnt,startCnt/regCnt startRate,\n" +
				"completeCnt,completeCnt/regCnt completeRate,\n" +
				"submitCnt,submitCnt/regCnt submitRate \n" +
				"from (\n" +
				"select a.homeCounty,sum(case when a.isProject='是'  then 1 else 0 end) regCnt,\n" +
				"sum(case when a.isProject='是' and b.isReach='是' then 1 else 0 end) reachCnt,\n" +
				"sum(case when a.isProject='是' and b.isDesign='是' then 1 else 0 end) designCnt,\n" +
				"sum(case when a.isProject='是' and b.isStart='是' then 1 else 0 end) startCnt,\n" +
				"sum(case when a.isProject='是' and b.isComplete='是' then 1 else 0 end) completeCnt,\n" +
				"sum(case when a.isProject='是' and b.isSubmit='是' then 1 else 0 end) submitCnt\n" +
				"  from jms_project a left join jms_project_build b\n" +
				"on a.pid=b.pid " +
				" where  COALESCE(b.isArchive,'否')='否' and a.isSelectConfirm='是' \n" +
				"group by a.homeCounty\n" +
				")t\n" + "\n";

		List<Record> buildSum = Db.find(sql);
		Db.update("delete a from jms_project_build_sum a where sumDate =?",sumDate);
		for(Record r:buildSum){
			Db.save("jms_project_build_sum",r);
		}
   		/*
   		1、谈点率排名更新
   		*/
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) reachSort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY reachRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.reachSort = b.reachSort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);
		/*
		2、设计完成率
		 */
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) designSort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY designRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.designSort = b.designSort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);
		/*
		3、开工率排名
		 */
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) startSort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY startRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.startSort = b.startSort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);
		/*
		4、完工率排名
		 */
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) completeSort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY completeRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.completeSort = b.completeSort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);
		/*
		5、交付率排名
		 */
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) submitSort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY submitRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.submitSort = b.submitSort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);
		/*
		6、本月计划完成率排名

		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) planCompleteSort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate ="+sumDate+" \n" +
				"ORDER BY planCompleteCnt/planCnt desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.planCompleteSort = b.planCompleteSort\n" +
				"where a.sumDate ="+sumDate;
		Db.update(sql);
		*/
		renderDWZSuccessJson("入库成功！");

	}

	/***
	 * 日报汇总
	 */

	public void exportCal(){

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String modifyDate = getPara("project.modifyDate");
		String sumDate = DateUtil.dateToStr(DateUtil.strToDate(modifyDate));
		String formerDate = DateUtil.getFormerDate(modifyDate);

		//工程超期
		String sql = "select a.homeCounty,count(*) cnt, \n" +
				"sum(case when b.isDesign='否' and DATEDIFF(NOW(),baseDate)>3 then 1 else 0 end) designCnt ,\n" +
				"sum(case when b.isStart='否' and DATEDIFF(NOW(),baseDate)>6 then 1 else 0 end) startCnt ,\n" +
				"sum(case when b.isComplete='否' and DATEDIFF(NOW(),baseDate)>46 then 1 else 0 end) completeCnt ,\n" +
				"sum(case when b.isSubmit='否' and DATEDIFF(NOW(),baseDate)>54 then 1 else 0 end) submitCnt \n" +
				"  from jms_project a left join jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				"where a.isProject='是'\n" +
				"GROUP BY homeCounty";
		List<Record> delaySum = Db.find(sql);

		//工程无进度
		sql = "select a.homeCounty,count(*) cnt,\n" +
				"sum(case when a.maxReachCnt=b.reachCnt then 1 else 0 end) reachDay,\n" +
				"sum(case when a.maxDesignCnt=b.designCnt then 1 else 0 end) designDay,\n" +
				"sum(case when a.maxStartCnt=b.startCnt then 1 else 0 end) startDay,\n" +
				"sum(case when a.maxCompleteCnt=b.completeCnt then 1 else 0 end) completeDay,\n" +
				"sum(case when a.maxSubmitCnt=b.submitCnt then 1 else 0 end) submitDay\n" +
				"from (\n" +
				"select homeCounty, max(reachCnt) maxReachCnt,max(designCnt) maxDesignCnt,max(startCnt) maxStartCnt,\n" +
				"max(completeCnt) maxCompleteCnt,max(submitCnt) maxSubmitCnt\n" +
				"from jms_project_build_sum\n" +
				"GROUP BY homeCounty) a\n" +
				"INNER JOIN jms_project_build_sum b\n" +
				"on a.homeCounty=b.homeCounty\n" +
				"GROUP BY homeCounty";
		List<Record> progressSum = Db.find(sql);

		//更新清单
		sql="select * from jms_project_build_detail where sumDate=?";
		List<Record> buildDetail = Db.find(sql,sumDate);
		//工程进度汇总
		sql="select * from jms_project_build_sum where sumDate=?";
		List<Record> buildSum = Db.find(sql,sumDate);

		/**
		 *点评
		 */
		sql = "select a.reachCnt,a.reachRate,(a.reachCnt-b.reachCnt) addReachCnt,\n" +
				"a.designCnt,a.designRate,(a.designCnt-b.designCnt) addDesignCnt, \n" +
				"a.startCnt,a.startRate,(a.startCnt-b.startCnt) addStartCnt, \n" +
				"a.completeCnt,a.completeRate,(a.completeCnt-b.completeCnt) addCompleteCnt, \n" +
				"a.submitCnt,a.submitRate,(a.submitCnt-b.submitCnt) addSubmitCnt, \n" +
				"a.submitCnt,a.submitRate,(a.submitCnt-b.submitCnt) addSubmitCnt, \n" +
				"a.submitCnt,a.submitRate,(a.submitCnt-b.submitCnt) addSubmitCnt \n" +
				"from \n(select sum(reachCnt) reachCnt,sum(reachCnt)/sum(regCnt) reachRate,\n" +
				"sum(designCnt) designCnt,sum(designCnt)/sum(regCnt) designRate,\n" +
				"sum(startCnt) startCnt,sum(startCnt)/sum(startCnt) startRate,\n" +
				"sum(completeCnt) completeCnt,sum(completeCnt)/sum(regCnt),completeRate,\n" +
				"sum(submitCnt) submitCnt,sum(submitCnt)/sum(regCnt),submitRate\n" +
				"  from jms_project_build_sum\n" +
				"where sumDate=" + sumDate + ") a,\n" +
				"(select sum(reachCnt) reachCnt,sum(reachCnt) /sum(regCnt) reachRate,\n" +
				"sum(designCnt) designCnt,sum(designCnt) /sum(regCnt) designRate,\n" +
				"sum(startCnt) startCnt,sum(startCnt) /sum(startCnt) startRate,\n" +
				"sum(completeCnt) completeCnt,sum(completeCnt) /sum(regCnt),completeRate,\n" +
				"sum(submitCnt) submitCnt,sum(submitCnt) /sum(regCnt),submitRate \n" +
				"  from jms_project_build_sum\n" +
				"where sumDate=" + formerDate +
				") b";
		List<Record> buildComment = Db.find(sql);

		//排名
		sql="select concat(max(reach1),'、',max(reach2),'、',max(reach3)) reach,\n" +
				"concat(max(design1),'、',max(design2),'、',max(design3)) design,\n" +
				"concat(max(start1),'、',max(start2),'、',max(start3)) start,\n" +
				"concat(max(complete1),'、',max(complete2),'、',max(complete3)) complete,\n" +
				"concat(max(submit1),'、',max(submit2),'、',max(submit3)) submit\n" +
				"from (\n" +
				"select case when a.reachSort=11 then homeCounty  end reach1 ,\n" +
				" case when reachSort=10 then homeCounty  end  reach2,\n" +
				" case when reachSort=9 then homeCounty  end  reach3,\n" +
				" case when designSort=11 then homeCounty  end design1,\n" +
				" case when designSort=10 then homeCounty  end  design2,\n" +
				" case when designSort=9 then homeCounty  end  design3,\n" +
				" case when startSort=11 then homeCounty  end start1,\n" +
				" case when startSort=10 then homeCounty  end  start2,\n" +
				" case when startSort=9 then homeCounty  end  start3,\n" +
				" case when completeSort=11 then homeCounty  end complete1,\n" +
				" case when completeSort=10 then homeCounty  end complete2,\n" +
				" case when completeSort=9 then homeCounty  end  complete3,\n" +
				" case when submitSort=11 then homeCounty  end submit1,\n" +
				" case when submitSort=10 then homeCounty  end  submit2,\n" +
				" case when submitSort=9 then homeCounty  end  submit3\n" +
				"from jms_project_build_sum a\n" +
				"where a.sumDate=? \n" +
				" )t";
		List<Record> buildSort = Db.find(sql,sumDate);

		sql="select distinct homeCounty  as homeCounty from jms_project_build_detail a\n" +
				"where a.sumDate=?";
		List<Record> buildCounty = Db.find(sql,sumDate);
		String homeCounty = "";
		for(Record r:buildCounty){
			homeCounty=homeCounty+'、'+r.get("homeCounty");
		}

		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("buildDetail", buildDetail);
		beans.put("buildSum", buildSum);

		beans.put("delaySum", delaySum);
		beans.put("progressSum", progressSum);

		beans.put("sumDate",sumDate);
		beans.put("homeCounty",homeCounty);
		beans.put("buildComment",buildComment);
		beans.put("buildSort",buildSort);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_cal.xls";

		String filename = "project_cal.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}

	public void export(){

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String homeCounty = getPara("project.homeCounty");
		String owner = getPara("project.owner");
		String demandOwner = getPara("project.demandOwner");
		String isProject = getPara("project.isProject");
		String isCm = getPara("project.isCm");
		String isCu = getPara("project.isCu");
		String isCt = getPara("project.isCt");
		String isArchive = getPara("project.isArchive");
		String taskMonth = getPara("project.taskMonth");


		String modifyDate = getPara("project.modifyDate");

		if(ChristStringUtil.isNotEmpty(owner)){
			whee.append(" and owner like ?");
			param.add("%" + owner + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner like ?");
			param.add("%" + demandOwner + "%");
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}
		if(ChristStringUtil.isNotEmpty(isProject)){
			whee.append(" and isProject = ?");
			param.add(isProject);
		}
		if(ChristStringUtil.isNotEmpty(isCm)){
			whee.append(" and isCm = ?");
			param.add(isCm);

		}
		if(ChristStringUtil.isNotEmpty(isCt)){
			whee.append(" and isCt = ?");
			param.add(isCt);
		}
		if(ChristStringUtil.isNotEmpty(isCu)){
			whee.append(" and isCu = ?");
			param.add(isCu);
		}
		if(ChristStringUtil.isNotEmpty(taskMonth)){
			whee.append(" and taskMonth = ?");
			param.add(taskMonth);
		}
		if(ChristStringUtil.isNotEmpty(isArchive)){
			whee.append(" and coalesce(b.isArchive,'否') = ?");
			param.add(isArchive);
		}

		if(ChristStringUtil.isNotEmpty(modifyDate)){
			whee.append(" and TO_DAYS(modifyDate) >= TO_DAYS(?)");
			param.add( modifyDate );
		}

		String sql="select a.homeCounty,a.name,a.cmName,a.ctName,a.cuName,a.code,a.longitude,a.latitude,a.addr," +
				"a.demandOwner,a.share,a.taskMonth,\n" +
				"a.isSelectConfirm,a.isProject,a.projectDate,a.projectOwner,a.isCm,a.isCt,a.isCu,\n" +
				"b.isReach,b.isStart,b.isDesign,b.isComplete,b.isSubmit,b.isArchive,b.modifyDate,b.modifyMemo,b.memo\n" +
				" from jms_project a\n" +
				"left join\n" +
				"jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				"where COALESCE(a.isPlus,'否')='否' and a.isTask='是'  "+ whee.toString()+" order by b.modifyDate desc";
		List<Record> buildDetail = Db.find(sql, param.toArray());

		sql = "select date_format(now(),'%Y%m%d') sumDate,homeCounty,notRegCnt,selConfirmCnt,reachCnt1,\n" +
				"regCnt,reachCnt2,reachCnt2/regCnt reachRate,\n" +
				"designCnt,designCnt/regCnt designRate,\n" +
				"startCnt,startCnt/regCnt startRate,\n" +
				"completeCnt,completeCnt/regCnt completeRate,\n" +
				"submitCnt,submitCnt/regCnt submitRate\n" +
				"from (\n" +
				"select a.homeCounty,\n" +
				"sum(case when a.isProject='否' then 1 else 0 end) notRegCnt,\n" +
				"sum(case when a.isProject='否' and b.isReach='是' then 1 else 0 end) reachCnt1,\n" +
				"sum(case when a.isProject='否' and a.isSelectConfirm='是' then 1 else 0 end) selConfirmCnt,\n" +
				"sum(case when a.isProject='是' then 1 else 0 end) regCnt,\n" +
				"sum(case when a.isProject='是' and b.isReach='是' then 1 else 0 end) reachCnt2,\n" +
				"sum(case when a.isProject='是' and b.isDesign='是' then 1 else 0 end) designCnt,\n" +
				"sum(case when a.isProject='是' and b.isStart='是' then 1 else 0 end) startCnt,\n" +
				"sum(case when a.isProject='是' and b.isComplete='是' then 1 else 0 end) completeCnt,\n" +
				"sum(case when a.isProject='是' and b.isSubmit='是' then 1 else 0 end) submitCnt \n" +
				"  from jms_project a left join jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				"where COALESCE(a.isPlus,'否')='否'  and a.isTask='是'  "+ whee.toString()+
				"group by a.homeCounty\n" +
				")t\n" +
				"\n";

		List<Record> buildSum = Db.find(sql,param.toArray());
		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("buildDetail", buildDetail);
		beans.put("buildSum", buildSum);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_build.xls";
		String filename = "project_build.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}

	public void exportSubmit()
	{
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList();

		String homeCounty = getPara("project.homeCounty");
		String projectOwner = getPara("project.projectOwner");
		if (ChristStringUtil.isNotEmpty(projectOwner))
		{
			whee.append(" and projectOwner like ?");
			param.add("%" + projectOwner + "%");
		}
		if (ChristStringUtil.isNotEmpty(homeCounty))
		{
			whee.append(" and homeCounty like ?");
			param.add("%" + homeCounty + "%");
		}
		String sql = "select '中国铁塔股份有限漳州市分公司' as zztt, a.homeCounty,a.name,a.code,a.addr,a.demandOwner,a.share,\n" +
				"a.isCm,a.isCt,a.isCu,a.inspectUnit,a.designUnit,a.buildUnit,a.auxUnit,b.startDate,b.completeDate,b.isPass,b.isSubmit \n" +
				" from jms_project a\nleft join\njms_project_build b\n" +
				"on a.pid=b.pid\nwhere b.isComplete='是' " + whee.toString() + " order by b.modifyDate desc";
		List<Record> buildDetail = Db.find(sql, param.toArray());

		Map<String, Object> beans = new HashMap();
		beans.put("buildDetail", buildDetail);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_submit.xls";
		String filename = "project_submit.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}

	@Before(AuthorizeInterceptor.class)
	public void delete() {
		String[] ids = getParaValues("ids");
		for(String id:ids){
			Project m =Project.dao.findById(id);
			Db.update("delete a from jms_project_build a where pid =?",m.get("pid"));
		}
		renderDWZSuccessJson("清除成功!");
	}

	@Before(AuthorizeInterceptor.class)
	public void archive()
	{
		String[] ids = getParaValues("ids");
		for (String id : ids) {
			try
			{
				String sql = "select a.pid,b.id,coalesce(b.isArchive,'否') isArchive" +
						" from jms_project a left join jms_project_build b on b.pid=a.pid  where a.id=?";
				ProjectBuild m = ProjectBuild.dao.findFirst(sql,id);
				String isArchive = m.getStr("isArchive");
				if (isArchive.equals("否")) {
					m.set("isArchive", "是");
				}
				ModelUtils.fillForUpdate(m);
				m.update();

			}
			catch (Exception e)
			{
				renderDWZSuccessJson("未完成，不能归档！");
				return;
			}
		}
		renderDWZSuccessJson("归档成功!");
	}
}